﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ECCIT.Data
{
    public enum SearchConditionJoinOperator
    {
        AND,
        OR,
    }

    public enum SearchConditionOperator
    {
        Equals,
        NotEquals,
        MoreThan,
        LessThan,
        NoLessThan,
        NoMoreThan,
        In,
        Like,
    }

    public interface IToSqlCommand
    {
        string ToSqlCommand();
    }

    [Serializable]
    public abstract class BaseSearchCondition : IToSqlCommand
    {
        public string ColumnName { get; set; }
        public DataType ColumnType { get; set; }
        public object ColumnValue { get; set; }
        public SearchConditionOperator Operator { get; set; }
        public SearchConditionList SubConditions { get; set; }

        public static readonly Dictionary<SearchConditionOperator, string> Operators = CreateSearchConditionOperators();

        private static Dictionary<SearchConditionOperator, string> CreateSearchConditionOperators()
        {
            Dictionary<SearchConditionOperator, string> operators = new Dictionary<SearchConditionOperator,string>();
            operators.Add (SearchConditionOperator.Equals, "=");
            operators.Add (SearchConditionOperator.NotEquals, "<>");
            operators.Add (SearchConditionOperator.LessThan, "<");
            operators.Add (SearchConditionOperator.MoreThan, ">");
            operators.Add (SearchConditionOperator.NoLessThan, ">=");
            operators.Add (SearchConditionOperator.NoMoreThan, "<=");
            operators.Add (SearchConditionOperator.In, " IN ");
            operators.Add (SearchConditionOperator.Like, " LIKE ");

            return operators;
        }

        public abstract string ToSqlCommand();
    }

    public class CommonSearchCondition : BaseSearchCondition
    {
        private CommonSearchCondition(string columnName, object columnValue, SearchConditionOperator conditionOperator)
        {
            this.ColumnName = columnName;
            this.ColumnValue = columnValue;
            this.Operator = conditionOperator;
        }

        public static CommonSearchCondition CreateInstance(string columnName, object columnValue, SearchConditionOperator conditionOperator)
        {
            return new CommonSearchCondition(columnName, columnValue, conditionOperator);
        }

        public override string ToSqlCommand()
        {
            StringBuilder builder = new StringBuilder ("");
            if (this.ColumnName.Trim () != string.Empty)
            {
                builder.Append (" " + this.ColumnName + BaseSearchCondition.Operators[this.Operator]);
                if (this.ColumnValue is Int16 || this.ColumnValue is Int32 || this.ColumnValue is Int64 || this.ColumnValue is decimal || this.ColumnValue is float)
                {
                    builder.Append (" " + this.ColumnValue + " ");
                }
                else if (this.ColumnValue is bool)
                {
                    if(DBConfigManager.DataBaseType == DataBaseType.OleDb)
                        builder.Append (" " + this.ColumnValue + " ");
                    else
                        builder.Append (" " + ((((bool)this.ColumnValue) == true) ? 1 : 0) + " ");
                }
                else if (this.ColumnValue is byte)
                {
                    if (DBConfigManager.DataBaseType == DataBaseType.OleDb)
                        builder.Append (" " + this.ColumnValue + " ");
                    else
                        builder.Append (" '" + this.ColumnValue + "' ");
                }
                else
                {
                    builder.Append ("'" + this.ColumnValue + "' ");
                }
            }

            if (this.SubConditions == null)
                return builder.ToString();

            builder.Append(" (");
            builder.Append(this.SubConditions.ToSqlCommand());
            builder.Append(") ");

            return builder.ToString();
        }
    }

    public class ParameterSearchCondition : BaseSearchCondition
    {
        private ParameterSearchCondition(string columnName, SearchConditionOperator conditionOperator)
        {
            this.ColumnName = columnName;
            this.ColumnValue = "#" + columnName;
            this.Operator = conditionOperator;
        }

        public static ParameterSearchCondition CreateInstance(string columnName, SearchConditionOperator conditionOperator)
        {
            return new ParameterSearchCondition(columnName, conditionOperator);
        }

        public override string ToSqlCommand()
        {
            StringBuilder builder = new StringBuilder("");
            if (this.ColumnName.Trim() != string.Empty)
            {
                builder.Append(" " + this.ColumnName + BaseSearchCondition.Operators[this.Operator]);
                builder.Append(this.ColumnValue + " ");
            }

            if (this.SubConditions == null)
                return builder.ToString();

            builder.Append(" (");
            builder.Append(this.SubConditions.ToSqlCommand());
            builder.Append(") ");

            return builder.ToString();
        }
    }

    public class InSearchCondition : BaseSearchCondition
    {
        private InSearchCondition (string columnName, DataType columnType, object columnValue)
        {
            // validate arguments
            if (columnName == null)
                throw new NullReferenceException("Column name cannot be null.");

            if (columnType == DataType.Binary || columnType == DataType.NText)
                throw new Exception("Column Type cannot be Binary or NText");

            this.ColumnName = columnName;
            this.ColumnType = columnType;   // columnType will be used to decide if '' will be used for columnValue
            this.ColumnValue = columnValue;
            this.Operator = SearchConditionOperator.In;
        }

        public static InSearchCondition CreateInstance (string columnName, DataType columnType, object columnValue)
        {
            return new InSearchCondition (columnName, columnType, columnValue);
        }

        public override string ToSqlCommand ()
        {
            StringBuilder builder = new StringBuilder ("");
            if (this.ColumnName.Trim() != string.Empty)
            {
                builder.Append(" " + this.ColumnName + BaseSearchCondition.Operators[this.Operator]).Append("(");

                JoinedString splitColumnValues = new JoinedString(ColumnValue.ToString(), ",");
                int i = 0;
                foreach (string cv in splitColumnValues)
                {
                    if (i > 0)
                        builder.Append(",");

                    if (this.ColumnType == DataType.BigInt || this.ColumnType == DataType.Int || this.ColumnType == DataType.SmallInt)
                        builder.Append(cv);
                    else
                        builder.Append("'").Append(cv).Append("'");

                    i++;
                }

                if (i == 0)
                    builder.Append("''");   // indicates no column value, so append '' for statement requirement

                builder.Append(") ");
            }
            
            if (this.SubConditions == null)
                return builder.ToString();

            builder.Append(" (");
            //int j = 0;
            //SubConditions.ForEach (delegate (BaseSearchCondition condition)
            //{
            //    if (j > 0)
            //        builder.Append (" ").Append (SubConditions.JoinOperator).Append (" ");

            //    builder.Append (condition.ToSqlCommand ());
            //    j++;
            //}
            //);
            builder.Append(this.SubConditions.ToSqlCommand());
            builder.Append (") ");

            return builder.ToString ();
        }
    }

    public class SearchConditionList : List<BaseSearchCondition>, IToSqlCommand
    {
        public SearchConditionJoinOperator JoinOperator { get; set; }

        public SearchConditionList(SearchConditionJoinOperator joinOperator)
        {
            this.JoinOperator = joinOperator;
        }

        public string ToSqlCommand()
        {
            StringBuilder builder = new StringBuilder();
            int i = 0;
            foreach (BaseSearchCondition sc in this)
            {
                if (i > 0)
                    builder.Append(this.JoinOperator.ToString());

                builder.Append(sc.ToSqlCommand());
                i++;
            }

            return builder.ToString();
        }
        
    }
}
